package com.xiyou.luckdraw.web.controller;

import com.xiyou.luckdraw.api.dal.entitys.AnswerBank;
import com.xiyou.luckdraw.api.dal.mapper.luckdraw.AnswerBank1Mapper;
import com.xiyou.luckdraw.api.dal.mapper.luckyelite.AnswerBank2Mapper;
import com.xiyou.luckdraw.common.result.ResponseData;
import com.xiyou.luckdraw.common.result.ResponseUtils;
import io.swagger.annotations.Api;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @ClassName: ExcelDemoController
 * @Description:
 * @author: limingxing
 * @Date: 2019-10-16 17:24
 */
@Slf4j
@RestController
@RequestMapping("/excel")
//@Api(value = "excel批量导入测试接口", tags = "excel批量导入测试接口")
public class ExcelDemoController {

    @Autowired
    AnswerBank1Mapper answerBank1Mapper;
    @Autowired
    AnswerBank2Mapper answerBank2Mapper;

    @PostMapping("/load")
    public ResponseData load(MultipartFile file, Integer type) throws Exception {
        System.out.println(file.getInputStream());
        List<AnswerBank> list = loadExcel("C:\\Users\\ASUS\\Desktop\\u.xls");
        batchInsert(list, type);
        return new ResponseUtils().setData("OK");
    }

    @RequestMapping(value="/upload",method=RequestMethod.POST)
   public String upload(@RequestParam(value="file",required=false)MultipartFile file,Integer type, HttpServletRequest request, HttpServletResponse response){
        List<AnswerBank> list = getExcelInfo(file);
        for (AnswerBank obj : list) {
            obj.setCreateTime(new Date());
            obj.setModifyTime(new Date());
            if (1 == type) {
                int row = answerBank1Mapper.insert(obj);
                System.out.println("----------" + row);
            } else {
                int row = answerBank2Mapper.insert(obj);
                System.out.println("----------" + row);
            }

        }
        return  "";
    }

    /**
     * 获取excel中的数据
     *
     * @param filepath
     * @return
     * @throws Exception
     */
    public static List<AnswerBank> loadExcel(String filepath) throws Exception {
        File file = new File(filepath);
        Workbook wb = WorkbookFactory.create(new FileInputStream(file));
        Sheet sheet = wb.getSheetAt(0);
        List<AnswerBank> datas = new ArrayList<>();//将数据添加到数据一行一行的添加到集合中，作为插入数据的入参
        Row row = null;
        for (int i = 1; i <= sheet.getLastRowNum(); i++) {
            System.out.println("i=" + i);
            row = sheet.getRow(i);
//            if(row == null || StringUtils.isEmpty(row.getCell(0).getStringCellValue())){
//                continue;
//            }
            AnswerBank data = new AnswerBank();
            data.setAnswerTitle(row.getCell(0) == null ? "" : row.getCell(0).getStringCellValue());
            data.setA(row.getCell(1) == null ? "" : row.getCell(1).getStringCellValue());
            data.setB(row.getCell(2) == null ? "" : row.getCell(2).getStringCellValue());
            data.setC(row.getCell(3) == null ? "" : row.getCell(3).getStringCellValue());
            data.setD(row.getCell(4) == null ? "" : row.getCell(4).getStringCellValue());
            data.setCorrectAnswer(row.getCell(5) == null ? 0 : new Long(row.getCell(5).getStringCellValue()));
            data.setLevel(row.getCell(6) == null ? 0 : new Long(row.getCell(6).getStringCellValue()));


            datas.add(data);

        }
        return datas;
    }

    //https://www.cnblogs.com/DFX339/p/10979048.html
    public void batchInsert(List<AnswerBank> datas, Integer type) {
        for (AnswerBank obj : datas) {
            obj.setCreateTime(new Date());
            obj.setModifyTime(new Date());
            if (1 == type) {
                int row = answerBank1Mapper.insert(obj);
                System.out.println("----------" + row);
            } else {
                int row = answerBank2Mapper.insert(obj);
                System.out.println("----------" + row);
            }

        }
    }


    /**
     * * 读EXCEL文件，获取信息集合 
     * * @param fielName 
     * * @return 
     */
    public List<AnswerBank> getExcelInfo(MultipartFile multipartFile) {
        String fileName = multipartFile.getOriginalFilename();//获取文件名  
        List<AnswerBank> answerList = new ArrayList<>();
        try {
            if (!validateExcel(fileName)) {// 验证文件名是否合格  
                return null;
            }
            boolean isExcel2003 = true;// 根据文件名判断文件是2003版本还是2007版本  
            if (isExcel2007(fileName)) {
                isExcel2003 = false;
            }
            answerList = createExcel(multipartFile.getInputStream(), isExcel2003);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return answerList;
    }

    /**
     *  
     *    * 根据excel里面的内容读取客户信息 
     *    * @param is 输入流 
     *    * @param isExcel2003 excel是2003还是2007版本 
     *    * @return 
     *    * @throws IOException 
     *    
     */
    public List<AnswerBank> createExcel(InputStream is, boolean isExcel2003) {
        try {
            Workbook wb = null;
            if (isExcel2003) {// 当excel是2003时,创建excel2003  
                wb = new HSSFWorkbook(is);
            } else {// 当excel是2007时,创建excel2007  
                wb = new XSSFWorkbook(is);
            }
            List<AnswerBank> answerList = readExcelValue(wb);// 读取Excel里面客户的信息  
            return answerList;
        } catch (IOException e) {
            e.printStackTrace();
        }
        return new ArrayList<>();
    }


    //总行数
    private int totalRows = 0;
    //总条数  
    private int totalCells = 0;

    /**
     *  
     *    * 读取Excel里面客户的信息 
     *    * @param wb 
     *    * @return 
     *    
     */
    private List<AnswerBank> readExcelValue(Workbook wb) {// 得到第一个shell  
        Sheet sheet = wb.getSheetAt(0);
// 得到Excel的行数  
        this.totalRows = sheet.getPhysicalNumberOfRows();
// 得到Excel的列数(前提是有行数)  
        if (totalRows > 1 && sheet.getRow(0) != null) {
            this.totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
        }
        List<AnswerBank> answerList = new ArrayList<AnswerBank>();
// 循环Excel行数  
        for (int r = 1; r < totalRows; r++) {
            Row row = sheet.getRow(r);
            if (row == null) {
                continue;
            }
            AnswerBank answer = new AnswerBank();// 循环Excel的列  
            for (int c = 0; c < this.totalCells; c++) {
                Cell cell = row.getCell(c);
                if (null != cell) {
                    if (c == 0) {//如果是纯数字,比如你写的是25,cell.getNumericCellValue()获得是25.0,通过截取字符串去掉.0获得25  
                        if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
                            String name = String.valueOf(cell.getNumericCellValue());
                            answer.setAnswerTitle(name.substring(0, name.length() - 2 > 0 ? name.length() - 2 : 1));//名称  
                        } else {
                            answer.setAnswerTitle(cell.getStringCellValue());//名称  
                        }
                  //      answer.setAnswerTitle(cell.getStringCellValue());
                        System.out.println("----------------");

                    } else if (c == 1) {
                        answer.setA(cell.getStringCellValue());
                    } else if (c == 2) {
                        answer.setB(cell.getStringCellValue());
                    }else if (c == 3) {
                        answer.setC(cell.getStringCellValue());
                    }else if (c == 4) {
                        answer.setD(cell.getStringCellValue());
                    }else if (c == 5) {
                        if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
                            String sex = String.valueOf(cell.getNumericCellValue());
                            answer.setCorrectAnswer(new Long(sex.substring(0, sex.length() - 2 > 0 ? sex.length() - 2 : 1)));//性别  
                        } else {
                            answer.setCorrectAnswer(new Long(cell.getStringCellValue()));
                        }

                    }else if (c == 6) {
                        if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
                            String sex = String.valueOf(cell.getNumericCellValue());
                            answer.setLevel(new Long(sex.substring(0, sex.length() - 2 > 0 ? sex.length() - 2 : 1)));//性别  
                        } else {
                            answer.setLevel(new Long(cell.getStringCellValue()));
                        }

//                        answer.setLevel(new Long(cell.getStringCellValue()));
                    }
                }
            }// 添加到list  
            answerList.add(answer);
        } return answerList;
    }


    /**
     *  
     *      * 验证EXCEL文件 
     *      *  
     *      * @param filePath 
     *      * @return 
     *      
     */
    public boolean validateExcel(String filePath) {
        if (filePath == null || !(isExcel2003(filePath) || isExcel2007(filePath))) {

            return false;
        }
        return true;
    }

    // @描述：是否是2003的excel，返回true是2003   
    public static boolean isExcel2003(String filePath) {
        return filePath.matches("^.+\\.(?i)(xls)$");
    }

    //@描述：是否是2007的excel，返回true是2007   
    public static boolean isExcel2007(String filePath) {
        return filePath.matches("^.+\\.(?i)(xlsx)$");
    }
}
